Building the Physical Layer of a Repository and Verify Connection in OBIEE

Physical Layer of a Repository 

1 . Select Tools > Update All Row Counts.

2 . When update row counts complete, move the cursor over the tables and observe that row count information is now visible, including when the row count was last updated.

If you want to enrich your career and become a professional in OBIEE, then visit Tekslate - a global online training platform: "OBIEE Training"   This course will help you to achieve excellence in this domain.

3 . Expand tables and observe that row count information is also visible for individual columns.

4 . Right-click a table and select View Data to view the data for the table.

5 . Close the View Data dialog box when you are done. It is a good idea to update row counts or view data after an import to verify connectivity. Viewing data or updating row count, if successful, tells you that your connection is configured correctly.

Create Aliases

1. It is recommended that you use table aliases frequently in the Physical layer to eliminate extraneous joins and to include best practice naming conventions for physical table names. Right-click SAMP_TIME_DAY_D and select New Object > Alias to open the Physical Table dialog box.
2. Enter D1 Time in the Name field.

3 . In the Description field, enter Time Dimension Alias at day grain. Stores one record for each day.

4 . Click the Columns tab. Note that alias tables inherit all column definitions from the source table.

5 . Click OK to close the Physical Table dialog box. 6 . Repeat the steps and create the following aliases for the remaining physical tables. SAMP_ADDRESSES_D = D4 Address SAMP_CUSTOMERS_D = D3 Customer SAMP_PRODUCTS_D = D2 Product SAMP_REVENUE_F = F1 Revenue

Create Keys and Joins

1 . Select the five alias tables in the Physical layer.

2 . Right-click one of the highlighted alias tables and select Physical Diagram > Selected Object(s) Only to open the Physical Diagram. Alternatively, you can click the Physical Diagram button on the toolbar.

3 . Rearrange the alias table objects so they are all visible.

4 . You may want to adjust the objects in the Physical Diagram. If so, use the toolbar buttons to zoom in, zoom out, fit the diagram, collapse or expand objects, select objects, and so forth:

5 . Click the New Join button on the toolbar.

6 . Click the F1 Revenue table and then the D1 Time table. The Physical Foreign Key dialog box opens. It matters which table you click first. The join creates a one-to-many (1:N) relationship that joins the key column in the first table to a foreign key column in the second table.

7 . Select the D1 Time. CALENDAR_DATE column, and then select F1 Revenue.BILL_DAY_DT to join the tables. Ensure that the Expression edit box (at the bottom) contains the following expression: "orcl".""."BISAMPLE"."D1 Time"."CALENDAR_DATE" = "orcl".""."BISAMPLE"."F1 Revenue"."BILL_DAY_DT"

8 . Click OK to close the Physical Foreign Key dialog box. The join is visible in the Physical Diagram.

Please be aware of the following upgrade considerations for Oracle BI EE 11g Release 1 (11.1.1.5): Joins in the Physical and Business Model diagrams are now represented by a line with an arrow at the "one" end of the join, rather than the line with crows feet at the "many" end of the join that was used in previous releases. When creating joins in the Physical and Business Model Diagrams, you now select the "many" end of the join first, and then select the "one" end of the join. In previous releases, joins in the diagrams were created by selecting the "one" end of the join first. 9 . Repeat the steps to create joins for the remaining tables. Use the following expressions as a guide. Please notice that D4 Address joins to D3 Customer. "orcl".""."BISAMPLE"."D2 Product"."PROD_KEY" = "orcl".""."BISAMPLE"."F1 Revenue"."PROD_KEY" "orcl".""."BISAMPLE"."D3 Customer"."CUST_KEY" = "orcl".""."BISAMPLE"."F1 Revenue"."CUST_KEY" "orcl".""."BISAMPLE"."D4 Address"."ADDRESS_KEY" = "orcl".""."BISAMPLE"."D3

10 . Click the Auto Layout button on the toolbar.

11 . Your diagram should look similar to the screenshot:.

12 . Click the X in the upper right corner to close the Physical Diagram. 13 . Select File > Save or click the Save button on the toolbar to save the repository.

14 . Click No when prompted to check global consistency. Checking Global Consistency checks for errors in the entire repository. Some of the more common checks are done in the Business Model and Mapping layer and Presentation layer. Since these layers are not defined yet, bypass this check until the other layers in the repository are built. You learn more about the consistency check later in this tutorial.

15 . Leave the Administration Tool and the repository open for the next topic. Congratulations! You have successfully created a new repository, imported a table schema from an external data source into the Physical layer, created aliases, and defined keys and joins. In the next topic, you learn how to build the Business Model and Mapping layer of a repository.

For an in-depth understanding of OBIEE click on

• OBIEE 11g Tutorials
• New Features in OBIEE11g
• Checking Out a Project in OBIEE 11g
• Making Changes to the Repository in OBIEE
• Setting Up a Multiuser Development Environment in OBIEE